package com.maxatime.poi;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;

import com.maxatime.pojo.entity.StudentBasicInfo;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;


/**
 * Author: Dreamer-1
 * Date: 2019-03-01
 * Time: 10:13
 * Description: 示例程序入口类
 */
public class MainTest {

    private static Logger logger = Logger.getLogger(MainTest.class.getName());

    public static void main(String[] args) {
    // public void main(String[] args) {

        /************** 读取Excel流程 ******************/
        // 设定Excel文件所在路径
        // String excelFileName = "C:/marmot/tmp/poitest.xlsx";
        String excelFileName = "F:\\\\Eclipse2023\\\\ExcelStudent.xlsx";
        // 读取Excel文件内容
        List<ExcelDataVO> readResult = ExcelReader.readExcel(excelFileName);
        System.out.println("readResult.size()::: " + readResult.size());
        StudentBasicInfo studentBasicInfo=new StudentBasicInfo();
        for(int i=1;i<readResult.size();i++) {
        	
        	Connection conn=null;
    		Statement stat=null;
        	
        	//1.导入jar包
        	try {
        		//2.注册驱动
        		Class.forName("com.mysql.jdbc.Driver");
        		//3.获取连接
				conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/springboot", "root", "root123");
				//4.获取执行sql执行者对象
				stat= conn.createStatement();
				//5.执行sql语句			
			       if(readResult.get(i).getId() == null) {
			    	   continue;
			       }else {
			    	   	studentBasicInfo.setStudentId(readResult.get(i).getId());
			        	studentBasicInfo.setStudentNum(readResult.get(i).getStudentNum());
			        	studentBasicInfo.setStudentName(readResult.get(i).getStudentName());
			        	studentBasicInfo.setMobile(readResult.get(i).getModel());
			        	studentBasicInfo.setSex(readResult.get(i).getSex());
			        	studentBasicInfo.setIdNum(readResult.get(i).getZhuceshijian());
			        	studentBasicInfo.setNativePlace(readResult.get(i).getNativePlace());
			        	studentBasicInfo.setAge(12);
			        	studentBasicInfo.setIdType("1");
			        	studentBasicInfo.setIdNum(readResult.get(i).getShenfenzheng());
			        	//生日 String类型转DataTime类型
			        	DateTime datetime = DateUtil.parse(readResult.get(i).getBirthday());
			        	studentBasicInfo.setBirthday(datetime);
			        	studentBasicInfo.setDeleteFlag("0");
			        	//家庭地址
			        	studentBasicInfo.setDetailAddr(readResult.get(i).getNativePlace());
			        	//创建时间，修改时间
			        	Date date = new Date();
			            DateTime dateTime1 = new DateTime(date);
			            studentBasicInfo.setCreateTime(dateTime1);
			            studentBasicInfo.setUpdateTime(dateTime1);
			            //邮箱
			        	studentBasicInfo.setEmail(readResult.get(i).getModel()+"@126.com");
					String sql="insert into student_basic_info (student_id,student_num,student_name,mobile,sex,age,id_type,id_num,birthday,delete_flag,create_time,update_time,detail_addr,email) values ('"+studentBasicInfo.getStudentId()+"','"+studentBasicInfo.getStudentNum()+"','"+studentBasicInfo.getStudentName()+"','"+studentBasicInfo.getMobile()+"','"+studentBasicInfo.getSex()+"','"+studentBasicInfo.getAge()+"','"+studentBasicInfo.getIdType()+"','"+studentBasicInfo.getIdNum()+"','"+studentBasicInfo.getBirthday()+"','"+studentBasicInfo.getDeleteFlag()+"','"+studentBasicInfo.getCreateTime()+"','"+studentBasicInfo.getUpdateTime()+"','"+studentBasicInfo.getDetailAddr()+"','"+studentBasicInfo.getEmail()+"')";
				  stat.executeUpdate(sql);}
				 //6.处理结果
				  //return count;
        	}catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	finally {
				//关闭资源
				if(conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				if(stat!=null) {
					try {
						stat.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
        	
        	
        }
        
        
        
        
        
        // todo 进行业务操作

        /************** 写入Excel流程 ******************/
        // 创建需要写入的数据列表
		/*
		 * List<ExcelDataVO> dataVOList = new ArrayList<>(2); ExcelDataVO dataVO = new
		 * ExcelDataVO(); dataVO.setName("小丽"); dataVO.setAge(18);
		 * dataVO.setLocation("广州"); dataVO.setJob("大学生"); ExcelDataVO dataVO2 = new
		 * ExcelDataVO(); dataVO2.setName("小英"); dataVO2.setAge(19);
		 * dataVO2.setLocation("深圳"); dataVO2.setJob("大学生"); dataVOList.add(dataVO);
		 * dataVOList.add(dataVO2);
		 * 
		 * // 写入数据到工作簿对象内 Workbook workbook = ExcelWriter.exportData(dataVOList);
		 * 
		 * // 以文件的形式输出工作簿对象 FileOutputStream fileOut = null; try { String exportFilePath
		 * = "D:/wendang/lianxi.xlsx"; File exportFile = new File(exportFilePath); if
		 * (!exportFile.exists()) { exportFile.createNewFile(); }
		 * 
		 * fileOut = new FileOutputStream(exportFilePath); workbook.write(fileOut);
		 * fileOut.flush(); } catch (Exception e) { logger.warning("输出Excel时发生错误，错误原因："
		 * + e.getMessage()); } finally { try { if (null != fileOut) { fileOut.close();
		 * } if (null != workbook) { workbook.close(); } } catch (IOException e) {
		 * logger.warning("关闭输出流时发生错误，错误原因：" + e.getMessage()); } }
		 */

    }

}
